import asyncio
import concurrent.futures
import io
import os
import shutil
import tempfile
import uuid
import zipfile
from datetime import datetime
from io import BytesIO
from pathlib import Path
from typing import Any, List, Dict
from urllib.parse import quote

import aiohttp
import nest_asyncio
import patoolib
import requests
import urllib3
from flask import Blueprint, request, jsonify, send_file, Response
from flask_cors import cross_origin
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Side, Border
from openpyxl.worksheet.worksheet import Worksheet
from werkzeug.datastructures import FileStorage

from app.db.crud.account import get_teacher_teaching_group, get_current_semester
from app.db.crud.query_material import query_unique_col_value_and_material_type, query_course_plans, \
    query_material_submissions
from app.db.crud.teacher_management_query import material_query, \
    course_query, user_query, department_query, classes_query
from app.decorators import require_params, role_required, login_required
from app.utils.exceptions import MissingParamError, InvalidRoleValueError, InternalServerError, InvalidParameterError, \
    ResourceNotFoundError
from app.utils.files import file
from app.utils.str_util import remove_trailing_parentheses, generate_week_serial_sql, str_to_bool, get_file_extension, \
    filtered_data, extract_week_number, extract_filename, get_content_type, parse_ai_check_result, extract_conclusion
from app.envConfig import ENV_CONFIG

material = Blueprint('material', __name__, url_prefix='/material')
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
nest_asyncio.apply()


@material.route("/submission", methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params('materialType', 'page', 'per_page')
def get_material_submissions():
    """
        分页查询教学资料提交记录

        从数据库中查询教学资料提交记录，返回指定页码的数据。

        Param:
            semester (str): 学期名称
            material_type (str): 资料类型（如：教案、课件等）
            page (int): 要查询的页码，从1开始计数
            per_page (int): 每页记录数，默认为10
            teacherId (可选str): 教师编号

        Returns:
            Dict: 包含数据：
                - data: {department, teacher, course, class_name, file_name, file_path}
                - meta_data: {current_page, total_count}
        """
    try:
        # 获取验证后的参数
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("validated_params")

        # 获取传参
        teacher_name = request.args.get("teacher", None)
        file_name = request.args.get("fileName", None)
        course_name = request.args.get("courseName", None)
        semester = request.args.get("semester", None)
        class_name = request.args.get("class_name", "all")
        department = request.args.get("department", "all")
        material_type = params.get("materialType")
        page = int(params.get("page"))
        per_page = int(params.get("per_page"))

        data = query_material_submissions(semester, class_name, department, material_type, teacher_name, file_name,
                                          course_name,
                                          page, per_page)

        return jsonify({
            "data": data.get("data"),
            "meta_data": {
                "current_page": page,
                "total_count": data.get("count"),
            }
        })
    except Exception as e:
        raise InternalServerError({"u-facingMsg": "服务器错误"}) from e


@material.route("/course-plans", methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params('class_name', 'course', 'teacher_name', 'semester')
def get_course_plans():
    """
        查询具体的授课计划

        从数据库中查询

        Param:
            class_name (str): 班级名称
            teacher_name (str): 教师名称
            semester (str): 学期名称

        Returns:
            Dict: 包含数据：
                - data: {department, teacher, course, class_name, file_name, file_path}
                - meta_data: {current_page, total_count}
        """
    try:
        # 获取验证后的参数
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("validated_params")

        # 获取传参
        class_name = params.get("class_name")
        course = params.get("course")
        teacher_name = params.get("teacher_name")
        semester = params.get("semester")

        file_name = query_course_plans(class_name, course, teacher_name, semester)

        return jsonify({
            "data": file_name,
        })
    except Exception as e:
        raise InternalServerError({"u-facingMsg": "服务器错误"}) from e


@material.route("/type_data_list", methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params('columnNameArr', 'materialType')
def get_material_department_data_list():
    try:
        # 获取验证后的参数
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("validated_params")

        # 获取传参
        col_name_list = params.get("columnNameArr")
        if not isinstance(col_name_list, list):
            raise MissingParamError("columnNameArr")
        material_type = params.get("materialType")

        data = {}
        for col_name in col_name_list:
            data[col_name] = (query_unique_col_value_and_material_type(col_name, material_type))

        return jsonify(data)
    except Exception as e:
        raise InternalServerError({"u-facingMsg": "服务器错误"}) from e


"""
以上为原有接口
============================================
以下为复刻java
"""


@material.route('/submission/school', methods=["GET"])
@login_required
@role_required('admin')
@require_params('role', 'semester', 'materialType')
def get_submission_by_school() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        semester = params.get("semester")
        material_type = params.get("materialType")

        if role != "admin":
            raise InvalidRoleValueError({"u-facingMsg": "登录角色异常！"})
        data = material_query.get_submission_by_school(semester, material_type)
        processed_data = filtered_data(data)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": processed_data,
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submission/dept', methods=["GET"])
@login_required
@role_required("department_head", "admin", "group_leader")
@require_params('role', 'deptId', 'semester', 'materialType')
def get_submission_by_dept() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        role = params.get("role")
        dept_id = params.get("deptId")
        semester = params.get("semester")
        material_type = params.get("materialType")

        if role not in ["department_head", "admin", "group_leader"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})
        data = get_submission_by_material_type_for_dept(dept_id, semester, material_type)
        processed_data = filtered_data(data)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": processed_data,
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submission/tgp', methods=["GET"])
@login_required
@role_required('group_leader')
@require_params('teacherId', 'tgpName', 'semester', 'materialType')
def get_submission_by_tgp() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        teacher_id = params.get("teacherId")
        tgp_name = params.get("tgpName")
        semester = params.get("semester")
        material_type = params.get("materialType")

        if get_teacher_teaching_group(teacher_id, get_current_semester()['id'])['is_leader'] != 1:
            InvalidRoleValueError({"u-facingMsg": "角色异常！"})
        if material_type == "teaching_plan":
            data = material_query.get_teaching_plan_submission_by_tgp(tgp_name, semester)
        elif material_type in ["score_report", "training_plan", "lesson_plan"]:
            data = material_query.get_course_submission_by_tgp(tgp_name, semester, material_type)
        else:
            data = material_query.get_submission_by_tgp(tgp_name, semester, material_type)
        processed_data = filtered_data(data)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": processed_data,
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submission/teacher', methods=["GET"])
@login_required
@role_required('all_teacher')
@require_params('teacherId', 'semester', 'courseName', 'className', 'materialType')
def get_submission_by_teacher() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        material_type = params.get("materialType")

        instructors = course_query.get_instructor_by_semester_course_class(semester, course_name, class_name)
        # 一门课多个教师上课的情况
        if '、' in instructors:
            cleaned_instructors = [remove_trailing_parentheses(inst) for inst in instructors.split('、')]
            tmp_data = []
            data = []
            for each_inst in cleaned_instructors:
                d = get_submission_by_material_type_for_teacher(each_inst, semester, course_name, class_name,
                                                                material_type)
                tmp_data += d
            if material_type == "teaching_plan":
                week_data = {}
                for row in tmp_data:
                    week = row["teaching_week"]
                    if week not in week_data:
                        week_data[week] = []
                    week_data[week].append(row)
                for week in range(1, 21):
                    submitted_record = [r for r in week_data[week] if r["submission_status"] == "已提交"]
                    if submitted_record:
                        data.append(submitted_record[0])
                    else:
                        data.append(week_data[week][0])
            else:
                data = tmp_data
        # 这是一门课单个教师的情况
        else:
            instructor = user_query.get_name_by_teacher_id(teacher_id).strip()
            data = get_submission_by_material_type_for_teacher(instructor, semester, course_name, class_name,
                                                               material_type)
        processed_data = filtered_data(data)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": processed_data,
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submission/school/first_check', methods=["GET"])
@login_required
@role_required('admin', 'department_head')
@require_params("role", "semester")
def get_submission_by_school_in_first_check() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        semester = params.get("semester")
        role = params.get("role")
        if role not in ["admin", "department_head"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        data = material_query.get_teaching_plan_submission_by_school_first_check(semester, generate_week_serial_sql(
            parse_current_week_range(semester)))
        processed_data = filtered_data(data)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": processed_data
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submission/dept/first_check', methods=["GET"])
@login_required
@role_required("admin", "department_head")
@require_params("semester", "role", "deptId")
def get_submission_by_dept_in_first_check() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        semester = params.get("semester")
        role = params.get("role")
        dept_id = params.get("deptId")
        if role not in ["admin", "department_head"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        data = material_query.get_teaching_plan_submission_by_dept_first_check(dept_id, semester,
                                                                               generate_week_serial_sql(
                                                                                   parse_current_week_range(semester)))
        processed_data = filtered_data(data)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": processed_data
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submission/tgp/first_check', methods=["GET"])
@login_required
@role_required('group_leader')
@require_params("teacherId", "tgpName", "semester")
def get_submission_by_tgp_in_first_check() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        teacher_id = params.get("teacherId")
        tgp_name = params.get("tgpName")
        semester = params.get("semester")
        if get_teacher_teaching_group(teacher_id, get_current_semester()['id'])['is_leader'] != 1:
            InvalidRoleValueError({"u-facingMsg": "角色异常！"})
        data = material_query.get_teaching_plan_submission_by_tgp_first_check(tgp_name, semester,
                                                                              generate_week_serial_sql(
                                                                                  parse_current_week_range(semester)))
        processed_data = filtered_data(data)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": processed_data
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/approval/dept', methods=["PUT"])
@login_required
@role_required('department_head')
@require_params('role', 'checkTeacherId', 'teacherId', 'semester', 'courseName', 'className', 'week', 'material_type',
                'check')
def approve_material_by_dept() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        check_teacher_id = params.get("checkTeacherId")
        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        week = int(params.get("week"))
        material_type = params.get("material_type")
        check = str_to_bool(params.get("check"))
        check_desc = request.args.get("checkDesc")

        if role != "department_head":
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        check_teacher_name = user_query.get_name_by_teacher_id(check_teacher_id).strip()
        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        if not teacher_name or not check_teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "教师信息异常"})

        if check_desc is None:
            check_desc = ''
        if user_query.is_tgp_leader(semester, check_teacher_name) and (
                teacher_name in user_query.get_teaching_group_info_by_tgp_leader(semester, check_teacher_name)[0].get(
            'teacher_full_time').split('、')):
            material_query.approve_material_by_dept(teacher_name, semester, course_name, class_name, week,
                                                    material_type, check, check_desc)
            material_query.approve_material_by_tgp(teacher_name, semester, course_name, class_name, week, material_type,
                                                   check, check_desc)
        else:
            material_query.approve_material_by_dept(teacher_name, semester, course_name, class_name, week,
                                                    material_type,
                                                    check,
                                                    check_desc)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "修改成功"
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/approval/tgp', methods=["PUT"])
@login_required
@role_required('group_leader')
@require_params(
    "teacherId", "approveTeacherId", "semester",
    "courseName", "className", "week", "check", "material_type"
)
def approve_material_by_tgp() -> Any:
    """
    教研组长审批教材接口
    Param:
        teacherId (str): 当前操作老师 ID
        approveTeacherId (str): 被审批老师 ID
        semester (str): 学期
        courseName (str): 课程名
        className (str): 班级名
        week (int): 周次
        check (bool): 是否通过
        checkDesc (str): 审核说明，可选
    Returns:
        Any: 修改成功或失败信息
    """
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        teacher_id = params.get("teacherId")
        approve_teacher_id = params.get("approveTeacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        week = int(params.get("week"))
        check = str_to_bool(params.get("check"))
        material_type = params.get("material_type")
        check_desc = request.args.get("checkDesc")

        # 判断是否为教研组组长
        if get_teacher_teaching_group(teacher_id, get_current_semester()['id'])['is_leader'] != 1:
            InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        teacher_name = user_query.get_name_by_teacher_id(approve_teacher_id).strip()
        if not teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "无教师姓名"})
        if check_desc is None:
            check_desc = ''
        material_query.approve_material_by_tgp(teacher_name, semester, course_name, class_name, week, material_type,
                                               check, check_desc)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "修改成功"
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/approval/dept/course', methods=["PUT"])
@login_required
@role_required('department_head')
@require_params("role", "teacherId", "semester", "courseName", "className", "check", "materialType")
def approve_material_by_dept_for_course() -> Any:
    """
    部门主任审批某课程的所有材料，或者用于审核非教学计划类型的教案
    ---
    请求参数:
        role: 用户角色，必须为 department_head
        teacherId: 被审批老师的 ID
        semester: 学期
        courseName: 课程名
        className: 班级
        check: 审批结果 (true/false)
        materialType: 材料类型
    """
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        check = str_to_bool(params.get("check"))
        material_type = params.get("materialType")

        if role != "department_head":
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        if not teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "教师信息异常！"})

        material_query.approve_material_by_dept_for_course(teacher_name, semester, course_name, class_name, check,
                                                           material_type)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "修改成功"
        })

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('approval/tgp/course', methods=["PUT"])
@login_required
@role_required('group_leader')
@require_params(
    "teacherId", "approveTeacherId", "semester",
    "courseName", "className", "check", "materialType"
)
def approve_material_by_tgp_for_course() -> Any:
    """
    教研组长审批教材（按课程维度）
    Param:
        teacherId (str): 当前操作老师 ID（需要具备教研组长权限）
        approveTeacherId (str): 被审批老师 ID
        semester (str): 学期
        courseName (str): 课程名
        className (str): 班级名
        check (bool): 是否通过
        materialType (str): 材料类型
    Returns:
        Any: 修改成功或失败信息
    """
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        teacher_id = params.get("teacherId")
        approve_teacher_id = params.get("approveTeacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        check = str_to_bool(params.get("check"))
        material_type = params.get("materialType")

        if get_teacher_teaching_group(teacher_id, get_current_semester()['id'])['is_leader'] != 1:
            InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        teacher_name = user_query.get_name_by_teacher_id(approve_teacher_id).strip()
        if not teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "无教师姓名"})

        material_query.approve_material_by_tgp_for_course(
            teacher_name, semester, course_name, class_name, check, material_type
        )
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "修改成功"
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submit/single', methods=["POST"])
@login_required
@role_required('all_teacher')
@require_params("teacherId", "semester", "courseName", "className", "week", "materialType", "file", include_files=True)
def submit_single_material() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        week = int(params.get("week"))
        material_type = params.get("materialType")
        file_one: FileStorage = params.get("file")

        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        if not teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "教师信息错误"})

        handle_single_material(teacher_name, semester, course_name, class_name, week, material_type, file_one)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "上传成功",
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submit/multiple', methods=["POST"])
@login_required
@role_required('all_teacher')
@require_params("teacherId", "semester", "courseName", "className", "materialType", "files", include_files=True)
def submit_multiple_material() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        material_type = params.get("materialType")
        archive_file: FileStorage = params.get("files")
        week_files = {}
        submitted_week = []
        if not archive_file:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件信息异常"})

        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        if not teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "教师信息异常"})

        # 支持 zip 和 rar 格式
        if not (archive_file.filename.endswith('.zip') or archive_file.filename.endswith('.rar')):
            raise InvalidParameterError({"u-facingMsg": "文件类型异常，仅支持ZIP和RAR格式"})

        with tempfile.TemporaryDirectory() as tmp_dir:
            extract_dir = Path(tmp_dir, "extracted")
            extract_dir.mkdir(exist_ok=True)

            try:
                archive_path = Path(tmp_dir, archive_file.filename)
                archive_file.save(archive_path)

                if archive_file.filename.endswith('.zip'):
                    with zipfile.ZipFile(archive_path, 'r') as zip_ref:
                        file_list = zip_ref.namelist()
                        for file_info in file_list:
                            try:
                                decoded_name = safe_decode_zip_filename(file_info)
                                target_path = extract_dir / decoded_name
                                if not decoded_name.endswith('/'):
                                    target_path.parent.mkdir(parents=True, exist_ok=True)
                                    with zip_ref.open(file_info) as source, open(target_path, 'wb') as target:
                                        target.write(source.read())
                            except Exception as e:
                                raise e

                elif archive_file.filename.endswith('.rar'):
                    patoolib.extract_archive(str(archive_path), outdir=str(extract_dir))

            except zipfile.BadZipFile as e:
                raise InvalidParameterError({"u-facingMsg": "ZIP文件损坏或格式不正确"})
            except Exception as e:
                raise InvalidParameterError({"u-facingMsg": f"文件解压失败: {str(e)}"})

            # 遍历解压后的文件，匹配多种命名格式
            for root, dirs, files in os.walk(extract_dir):
                for f in files:
                    file_path = Path(root, f)
                    if file_path.suffix.lower() in ['.docx']:
                        file_name = file_path.stem
                        week_num = extract_week_number(file_name)
                        if week_num is not None:
                            week_files[week_num] = file_path

            # 处理找到的文件
            for week in range(1, 21):
                if week in week_files:
                    submitted_week.append(week)
                    file_path = week_files[week]
                    with open(file_path, 'rb') as f:
                        file_storage = FileStorage(
                            stream=f,
                            filename=f"{week}.docx",
                            name="file",
                            content_type="application/vnd.openxmlformats-officedocument.wordprocessingml.document"
                        )
                        handle_single_material(teacher_name, semester, course_name, class_name, week, material_type,
                                               file_storage)
        return jsonify({
            "code": 200,
            "message": "success",
            "success": "批量上传成功",
            "submitted_weeks": submitted_week
        })

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submit/all', methods=["POST"])
@login_required
@role_required('all_teacher')
@require_params("semester", "teacherId", "files", include_files=True)
def submit_all_material():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        semester = params.get("semester")
        teacher_id = params.get("teacherId")
        archive_file: FileStorage = params.get("files")

        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        if not teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "教师信息异常"})

        # 生成会话ID
        session_id = None
        temp_dir = None

        success_files = []
        fail_files = []

        # 支持 zip 和 rar 格式
        if not (archive_file.filename.endswith('.zip') or archive_file.filename.endswith('.rar')):
            raise InvalidParameterError({"u-facingMsg": "文件类型异常，仅支持ZIP和RAR格式"})

        with tempfile.TemporaryDirectory() as tmp_dir:
            extract_dir = Path(tmp_dir, "extracted")
            extract_dir.mkdir(exist_ok=True)

            try:
                archive_path = Path(tmp_dir, archive_file.filename)
                archive_file.save(archive_path)

                if archive_file.filename.endswith('.zip'):
                    with zipfile.ZipFile(archive_path, 'r') as zip_ref:
                        file_list = zip_ref.namelist()
                        for file_info in file_list:
                            try:
                                decoded_name = safe_decode_zip_filename(file_info)
                                target_path = extract_dir / decoded_name
                                if not decoded_name.endswith('/'):
                                    target_path.parent.mkdir(parents=True, exist_ok=True)
                                    with zip_ref.open(file_info) as source, open(target_path, 'wb') as target:
                                        target.write(source.read())
                            except Exception as e:
                                raise e

                elif archive_file.filename.endswith('.rar'):
                    patoolib.extract_archive(str(archive_path), outdir=str(extract_dir))

            except zipfile.BadZipFile as e:
                raise InvalidParameterError({"u-facingMsg": "ZIP文件损坏或格式不正确"})
            except Exception as e:
                raise InvalidParameterError({"u-facingMsg": f"文件解压失败: {str(e)}"})

            # 遍历解压后的文件，匹配多种命名格式
            for root, dirs, files in os.walk(extract_dir):
                for f in files:
                    file_path = Path(root, f)
                    try:
                        # 获得文件名中的所有信息
                        material_info: Dict = extract_filename(f)
                        if not material_info:
                            raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件名解析失败"})

                        if material_info['semester'] != semester:
                            raise ResourceNotFoundError(extra_details={"u-facingMsg": "学期不匹配"})

                        # 根据文件扩展名设置正确的content_type
                        content_type = get_content_type(f)
                        # 处理单个文件上传
                        with open(file_path, 'rb') as file_obj:
                            file_storage = FileStorage(
                                stream=file_obj,
                                filename=f,
                                content_type=content_type
                            )
                            handle_single_material(
                                material_info["teacher_name"],
                                semester,
                                material_info["course_name"],
                                material_info["class_name"],
                                material_info["week"],
                                material_info["material_type"],
                                file_storage
                            )

                        success_files.append({
                            "filename": f,
                            "status": "success",
                            "message": "文件上传成功"
                        })

                    except Exception as e:
                        # 获取用户友好的错误信息
                        if hasattr(e, 'details') and isinstance(e.details, dict) and 'u-facingMsg' in e.details:
                            error_msg = e.details['u-facingMsg']
                        else:
                            error_msg = str(e)

                        if session_id is None:
                            session_id = str(uuid.uuid4())
                            temp_dir = Path("/opt/tmp/material_tmp") / session_id
                            temp_dir.mkdir(parents=True, exist_ok=True)

                        # 将失败的文件复制到临时目录
                        temp_file_path = temp_dir / f
                        shutil.copy2(file_path, temp_file_path)
                        fail_files.append({
                            "original_filename": f,
                            "error_message": error_msg,
                            "temp_path": str(temp_file_path)
                        })

        # 返回结果
        result = {
            "code": 200,
            "message": "success",
            "session_id": session_id,
            "success_count": len(success_files),
            "failed_count": len(fail_files),
            "success_files": success_files,
            "failed_files": fail_files
        }

        return jsonify(result)

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submit/rename', methods=["POST"])
@login_required
@role_required('all_teacher')
@require_params("session_id", "rename_list")
def submit_rename_material():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        session_id = params.get("session_id")
        rename_list: List[Dict] = params.get("rename_list")

        temp_dir = Path("/opt/tmp/material_tmp") / session_id
        if not temp_dir.exists():
            raise InvalidParameterError({"u-facingMsg": "会话ID无效或已过期"})

        results = []
        remaining_files = []

        for rename_item in rename_list:
            original_filename = rename_item.get("original_filename")
            new_filename = rename_item.get("new_filename")

            temp_file_path = temp_dir / original_filename

            if not temp_file_path.exists():
                raise ResourceNotFoundError({"u-facingMsg": "文件资源不存在"})

            try:
                # 验证新文件名是否符合规范
                material_info: Dict = extract_filename(new_filename)
                if not material_info:
                    raise ResourceNotFoundError({"u-facingMsg": "新文件名格式不符合规范"})

                # 根据新文件名的扩展名设置正确的content_type
                content_type = get_content_type(new_filename)

                # 处理文件上传
                with open(temp_file_path, 'rb') as file_obj:
                    file_storage = FileStorage(
                        stream=file_obj,
                        filename=new_filename,
                        content_type=content_type
                    )
                    handle_single_material(
                        material_info["teacher_name"],
                        material_info["semester"],
                        material_info["course_name"],
                        material_info["class_name"],
                        material_info["week"],
                        material_info["material_type"],
                        file_storage
                    )

                # 删除临时文件
                temp_file_path.unlink()

                results.append({
                    "original_filename": original_filename,
                    "new_filename": new_filename,
                    "status": "success",
                    "message": "文件重命名并上传成功"
                })

            except Exception as e:
                # 获取用户友好的错误信息
                if hasattr(e, 'details') and isinstance(e.details, dict) and 'u-facingMsg' in e.details:
                    error_msg = e.details['u-facingMsg']
                else:
                    error_msg = str(e)

                results.append({
                    "original_filename": original_filename,
                    "new_filename": new_filename,
                    "status": "failed",
                    "error_message": error_msg
                })

                # 保留在临时目录中
                remaining_files.append({
                    "original_filename": original_filename,
                    "error_message": error_msg,
                    "temp_path": str(temp_file_path)
                })

        # 检查临时目录是否为空，如果为空则删除
        if not any(temp_dir.iterdir()):
            shutil.rmtree(temp_dir)

        # 返回结果
        return jsonify({
            "code": 200,
            "message": "success",
            "results": results,
            "remaining_files": remaining_files
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route("/submit/merge", methods=["POST"])
@login_required
@role_required('all_teacher')
@require_params("submitTeacherId", "semester", "courseName", "materialType", "file", include_files=True)
def submit_merge_material():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        submit_teacher_id = params.get("submitTeacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        material_type = params.get("materialType")
        teacher_name = user_query.get_name_by_teacher_id(submit_teacher_id).strip()
        if not teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "无教师姓名"})

        file_one: FileStorage = params.get("file")
        if file_one is None or file_one.filename == '':
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件为空"})
        user_dept = user_query.get_belong(teacher_name, semester)
        if user_dept:
            dept_name = user_dept.get("department_name")
            file_path = file.get_workdir_file_path(semester, dept_name, course_name, "", "", "")
            file.upload_file(file_one, file_path, file_one.filename)
            teacher_list = file.get_merge_material_teacher_list(file_one.filename)
            class_list = file.get_merge_material_class_list(file_one.filename)
            for teacher_id in teacher_list:
                teacher_name = user_query.get_name_by_teacher_id(teacher_id)
                for class_name in class_list:
                    material_query.submit(teacher_name, semester, course_name, class_name, -1, material_type,
                                          str(Path(file_path, file_one.filename)))
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "上传成功",
        })

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route("/download/single", methods=["GET"])
@login_required
@role_required('all_teacher')
@cross_origin(expose_headers=['Content-Disposition'])
@require_params("teacherId", "semester", "courseName", "className", "week", "materialType")
def get_single_material() -> Any:
    try:
        if hasattr(request, "validated_params"):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        teacher_id = params.get("teacherId").strip()
        semester = params.get("semester").strip()
        course_name = params.get("courseName").strip()
        class_name = params.get("className").strip()
        week = int(params.get("week"))
        material_type = params.get("materialType").strip()
        file_path = ''
        if ',' not in teacher_id:
            teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
            if not teacher_name:
                raise ResourceNotFoundError(extra_details={"u-facingMsg": "获取教师信息失败"})

            cour_dept_and_inst = course_query.get_course_department_and_instructor_by_semester_and_course_name_and_class_name(
                semester, course_name, class_name)
            course_dept = cour_dept_and_inst["course_department"]
            if not course_dept:
                raise ResourceNotFoundError(extra_details={"u-facingMsg": "获取课程所属系部失败"})

            file_path = Path(
                material_query.get_file_path('', semester, course_name, class_name, week, material_type)).resolve()
        else:
            for teacher_id in teacher_id.split(','):
                teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
                if not teacher_name:
                    raise ResourceNotFoundError(extra_details={"u-facingMsg": "获取教师信息失败"})
                if material_query.get_submission_existence(teacher_name, semester, course_name, class_name, week,
                                                           material_type):
                    file_path = Path(material_query.get_file_path('', semester, course_name, class_name, week,
                                                                  material_type)).resolve()
                    break

        encoded_name = quote(file_path.name, safe='')  # safe='' 表示连 / 都编码，确保安全

        # 构造 Content-Disposition
        # 注意：filename="..." 中的内容是 URL 编码的，所以整体仍是 ASCII
        content_disposition = (
            f'attachment; '
            f'filename="{encoded_name}"; '
            f"filename*=utf-8''{encoded_name}"
        )
        # 读取文件并返回
        with open(file_path, 'rb') as f:
            data = f.read()
        return Response(
            data,
            mimetype='application/octet-stream',
            headers={'Content-Disposition': content_disposition}
        )
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route("/download/multiple", methods=["GET"])
@login_required
@role_required('all_teacher')
@cross_origin(expose_headers=['Content-Disposition'])
@require_params("semester", "materialType", "deptId", "week")
def get_multiple_material():
    try:
        if hasattr(request, "validated_params"):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        semester = params.get("semester")
        material_type = params.get("materialType")
        dept_id = params.get("deptId")
        week = params.get("week")

        if dept_id != "all":
            dept_name = department_query.get_dept_name_by_id(dept_id)
            if not dept_name:
                raise ResourceNotFoundError(extra_details={"u-facingMsg": "部门信息缺失"})
            file_path_list = material_query.get_file_path_list_by_semester_and_dept_name_and_material_type_and_week(
                semester, dept_name, material_type, week if week != "all" else None
            )
            zip_filename = f"{semester}_{dept_name}_{ENV_CONFIG.material_type_mapping.get(material_type)}_第{week}周.zip"
        else:
            file_path_list = material_query.get_file_path_list_by_semester_and_dept_name_and_material_type_and_week(
                semester, '', material_type, week if week != "all" else None
            )
            zip_filename = f"{semester}_全部系部_{ENV_CONFIG.material_type_mapping.get(material_type)}_第{week}周.zip"
        # 创建内存中的ZIP文件
        zip_buffer = io.BytesIO()

        with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
            for file_path in file_path_list:
                source_path = Path(file_path)
                if source_path.exists() and source_path.is_file():
                    # 构建ZIP内的文件路径
                    if dept_id != "all":
                        arc_name = f"{semester}/{dept_name}/{source_path.name}"
                    else:
                        arc_name = f"{semester}/{source_path.name}"
                    # 直接将文件添加到ZIP中，不经过磁盘复制
                    zip_file.write(source_path, arc_name)
                else:
                    raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件数据异常1"})

        # 检查ZIP文件中是否有文件
        if zip_file.namelist():
            zip_buffer.seek(0)
            encoded_name = quote(zip_filename, safe='')
            response = send_file(
                zip_buffer,
                as_attachment=True,
                download_name=zip_filename,
                mimetype='application/zip'
            )

            response.headers[
                'Content-Disposition'] = f'attachment; filename="{encoded_name}"; filename*=UTF-8\'\'{encoded_name}'
        else:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件数据异常2"})
        return response

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/export/department/material', methods=["GET"])
@login_required
@role_required('admin', 'departmen_head', 'group_leader')
@require_params('role', 'deptId', 'semester', 'materialType')
def export_department_material_by_admin():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        role = params.get("role")
        dept_id = params.get("deptId")
        semester = params.get("semester")
        material_type = params.get("materialType")

        if role not in ["admin", "department_head", "group_leader"]:
            raise InvalidRoleValueError({"u-facingMsg": "用户角色异常！"})

        data = get_submission_by_material_type_for_dept(dept_id, semester, material_type)

        dept_name = department_query.get_dept_name_by_id(dept_id)

        wb = Workbook()
        ws = wb.active

        center_alignment = Alignment(horizontal="center", vertical="center")
        thin_side = Side(border_style="thin", color="000000")
        thin_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
        songti_font = Font(name="宋体", size=12)

        ws.title = f"{dept_name}--系部教学材料提交信息"

        for col in ['A', 'D', 'E', 'F']:
            ws.column_dimensions[col].width = 15
        ws.column_dimensions['B'].width = 20
        ws.column_dimensions['C'].width = 40
        ws.column_dimensions['G'].width = 30

        ws.merge_cells("A1:G1")
        cell = ws["A1"]
        cell.value = f"{dept_name}--系部教学材料提交信息--{ENV_CONFIG.material_type_mapping[material_type]}"
        style_cell(cell, Font(name="楷体", size=20))

        ws.merge_cells("A2:E2")
        cell = ws["A2"]
        ws.row_dimensions[2].height = 20
        style_cell(cell, Font(name="宋体", size=15))
        cell.value = f"系部名称：{dept_name}"
        ws.merge_cells("F2:G2")
        cell = ws["F2"]
        style_cell(cell, Font(name="宋体", size=15))

        list_row3 = ["班级", "课程名称", "专业名称", "教学周", "教师姓名", "提交状态", "提交时间"]
        for i in range(0, 7):
            cell = ws.cell(row=3, column=i + 1, value=list_row3[i])
            cell.alignment = center_alignment

        for i, data_row in enumerate(data, start=4):
            for col, value in enumerate([
                data_row.get("class_name"),
                data_row.get("course_name"),
                data_row.get("major"),
                data_row.get("teaching_week"),
                data_row.get("name"),
                data_row.get("submission_status"),
                data_row.get("submit_time", "")
            ], start=1):
                cell = ws.cell(row=i, column=col, value=value)
                cell.alignment = center_alignment
                cell.border = thin_border
                cell.font = songti_font

        output = BytesIO()
        wb.save(output)
        output.seek(0)

        return send_file(
            output,
            as_attachment=True,
            download_name=f"{dept_name}--系部教学材料提交信息--{ENV_CONFIG.material_type_mapping[material_type]}.xlsx"
        )

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/submission/remove', methods=["DELETE"])
# @login_required
@role_required('all_teacher')
@require_params("teacherId", "semester", "course_name", "class_name", "week",
                "material_type")
def remove_submission():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("course_name")
        class_name = params.get("class_name")
        week = params.get("week")
        material_type = params.get("material_type")
        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        if not teacher_name:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "教师信息异常"})
        # 先查找是否存在,如果存在则删除旧文件,然后再删除数据库中的数据
        if material_query.get_submission_existence(teacher_name, semester, course_name, class_name, week,
                                                   material_type):
            remove_origin_file(teacher_name, semester, course_name, class_name, week, material_type)
            material_query.remove_teaching_material(teacher_name, semester, course_name, class_name,
                                                    week, material_type)
            return jsonify({
                "code": 200,
                "message": "success",
                "success": "删除成功",
            })
        else:
            return jsonify({
                "code": 500,
                "message": "false",
                "success": "删除失败"
            })

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/export/introspection/report', methods=["GET"])
# @login_required
# @role_required('admin')
@require_params("semester", "deptId", "materialType")
def export_introspection_report():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        semester = params.get("semester")
        dept_id = params.get("deptId")
        dept_name = department_query.get_dept_name_by_id(dept_id)
        teacher_id = request.args.get("teacherId", "")
        material_type = params.get("materialType")
        report_name = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-反思检查报告'

        # 将数据写入Excel文件
        wb = Workbook()
        ws: Worksheet = wb.active
        center_alignment = Alignment(horizontal="center", vertical="center")
        thin_side = Side(border_style="thin", color="000000")
        thin_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
        songti_font = Font(name="宋体", size=10)
        ws.title = report_name

        ws.column_dimensions['A'].width = 20
        ws.column_dimensions['B'].width = 20
        ws.column_dimensions['C'].width = 20
        ws.column_dimensions['D'].width = 20
        ws.column_dimensions['E'].width = 20
        ws.column_dimensions['F'].width = 10
        ws.column_dimensions['G'].width = 50
        ws.column_dimensions['H'].width = 25
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=9)
        cell = ws.cell(row=1, column=1,
                       value=report_name)
        cell.alignment = center_alignment
        cell.font = Font(name="宋体", size=24, bold=True)
        list_field = ["学期", "课程所属系部", "教师姓名", "班级", "课程", "对应周数", "反思检查信息",
                      "反思检查时间"]
        for i in range(1, 9):
            cell = ws.cell(row=2, column=i, value=list_field[i - 1])
            cell.alignment = center_alignment
            cell.font = songti_font
        # 获取数据
        if not teacher_id:
            data = get_submission_by_material_type_for_dept(dept_id, semester, material_type)
            download_name = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-反思检查报告.xlsx'
        else:
            teacher_name = user_query.get_name_by_teacher_id(teacher_id)
            data = material_query.get_all_submissions_by_teacher(semester, teacher_name, material_type)
            ws.title = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-{teacher_name}-反思检查报告'
            download_name = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-{teacher_name}-反思检查报告.xlsx'
        for i, row in enumerate(data, start=3):
            result = send_introspection_request(row.get('file_path'))
            result.get('answer', '') if result else result
            for col, value in enumerate([
                semester,
                dept_name,
                row.get("teacher_name"),
                row.get("class_name"),
                row.get("course_name"),
                row.get("week"),
                str(result).replace('True', '有反思').replace('False', '无反思'),
                datetime.now()
            ], start=1):
                cell = ws.cell(row=i, column=col, value=value)
                cell.alignment = center_alignment
                cell.border = thin_border
                cell.font = songti_font
        output = BytesIO()
        wb.save(output)
        output.seek(0)
        return send_file(
            output,
            as_attachment=True,
            download_name=download_name
        )

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/export/content/report', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params("semester", "deptId", "materialType")
def export_content_report():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        semester = params.get("semester")
        dept_id = params.get("deptId")
        dept_name = department_query.get_dept_name_by_id(dept_id)
        teacher_id = request.args.get("teacherId", "")
        material_type = params.get("materialType")
        download_name = ''
        # 将数据写入Excel文件
        wb = Workbook()
        ws: Worksheet = wb.active
        center_alignment = Alignment(horizontal="center", vertical="center")
        thin_side = Side(border_style="thin", color="000000")
        thin_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
        songti_font = Font(name="宋体", size=10)
        ws.title = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-内容检查报告'

        ws.column_dimensions['A'].width = 20
        ws.column_dimensions['B'].width = 20
        ws.column_dimensions['C'].width = 20
        ws.column_dimensions['D'].width = 20
        ws.column_dimensions['E'].width = 20
        ws.column_dimensions['F'].width = 10
        ws.column_dimensions['G'].width = 50
        ws.column_dimensions['H'].width = 20
        ws.column_dimensions['I'].width = 25
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=9)
        cell = ws.cell(row=1, column=1,
                       value=f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-内容检查报告')
        cell.alignment = center_alignment
        cell.font = Font(name="宋体", size=24, bold=True)
        list_field = ["学期", "课程所属系部", "教师姓名", "班级", "课程", "对应周数", "内容检查信息", "内容检查结果",
                      "内容检查时间"]
        for i in range(1, 10):
            cell = ws.cell(row=2, column=i, value=list_field[i - 1])
            cell.alignment = center_alignment
            cell.font = songti_font

        # 获取数据
        if not teacher_id:
            data = get_submission_by_material_type_for_dept(dept_id, semester, material_type)
            download_name = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-内容检查报告.xlsx'
        else:
            teacher_name = user_query.get_name_by_teacher_id(teacher_id)
            data = material_query.get_all_submissions_by_teacher(semester, teacher_name, material_type)
            ws.title = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-{teacher_name}-内容检查报告'
            download_name = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-{teacher_name}-内容检查报告.xlsx'
        for i, row in enumerate(data, start=3):
            check = send_content_request(row.get('file_path'))
            check_detail = check.get('data').get('check_result') if check else ''
            check_result = extract_conclusion(check_detail) if check_detail else ''
            for col, value in enumerate([
                semester,
                dept_name,
                row.get("teacher_name"),
                row.get("class_name"),
                row.get("course_name"),
                row.get("week"),
                check_detail,
                check_result,
                datetime.now(),
            ], start=1):
                cell = ws.cell(row=i, column=col, value=value)
                cell.alignment = center_alignment
                cell.border = thin_border
                cell.font = songti_font
        output = BytesIO()
        wb.save(output)
        output.seek(0)
        return send_file(
            output,
            as_attachment=True,
            download_name=download_name
        )

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/export/comparison/report', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params("semester", "deptId", "materialType")
def export_comparison_report():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        semester = params.get("semester")
        dept_id = params.get("deptId")
        dept_name = department_query.get_dept_name_by_id(dept_id)
        teacher_id = request.args.get("teacherId", "")
        material_type = params.get("materialType")
        download_name = ''
        # 将数据写入Excel文件
        wb = Workbook()
        ws: Worksheet = wb.active
        center_alignment = Alignment(horizontal="center", vertical="center")
        thin_side = Side(border_style="thin", color="000000")
        thin_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
        songti_font = Font(name="宋体", size=10)
        ws.title = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-对比检查报告'

        ws.column_dimensions['A'].width = 20
        ws.column_dimensions['B'].width = 20
        ws.column_dimensions['C'].width = 20
        ws.column_dimensions['D'].width = 20
        ws.column_dimensions['E'].width = 20
        ws.column_dimensions['F'].width = 10
        ws.column_dimensions['G'].width = 50
        ws.column_dimensions['H'].width = 20
        ws.column_dimensions['I'].width = 25
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=9)
        cell = ws.cell(row=1, column=1,
                       value=f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-对比检查报告')
        cell.alignment = center_alignment
        cell.font = Font(name="宋体", size=24, bold=True)
        list_field = ["学期", "课程所属系部", "教师姓名", "班级", "课程", "对应周数", "对比检查信息", "对比检查结果",
                      "对比检查时间"]
        for i in range(1, 10):
            cell = ws.cell(row=2, column=i, value=list_field[i - 1])
            cell.alignment = center_alignment
            cell.font = songti_font

        # 获取数据
        if not teacher_id:
            data = get_submission_by_material_type_for_dept(dept_id, semester, material_type)
            download_name = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-对比检查报告.xlsx'
        else:
            teacher_name = user_query.get_name_by_teacher_id(teacher_id)
            data = material_query.get_all_submissions_by_teacher(semester, teacher_name, material_type)
            ws.title = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-{teacher_name}-对比检查报告'
            download_name = f'{semester}-{dept_name}-{ENV_CONFIG.material_type_mapping.get(material_type)}-{teacher_name}-对比检查报告.xlsx'
        for i, row in enumerate(data, start=3):
            file_path = row.get('file_path')
            course_name = row.get('course_name')
            class_name = row.get('class_name')
            checked_teacher_name = row.get('teacher_name')
            lesson_plan_file_path = material_query.get_file_path(checked_teacher_name, semester, course_name,
                                                                 class_name, -1, 'lesson_plan')
            file_path = f'{file_path},{lesson_plan_file_path}'
            check = send_comparison_request(file_path)
            check_detail = check.get('data').get('check_result') if check else ''
            check_result = extract_conclusion(check_detail) if check_detail else ''
            for col, value in enumerate([
                semester,
                dept_name,
                row.get("teacher_name"),
                row.get("class_name"),
                row.get("course_name"),
                row.get("week"),
                check_detail,
                check_result,
                datetime.now(),
                # 发送反思审查的请求
            ], start=1):
                cell = ws.cell(row=i, column=col, value=value)
                cell.alignment = center_alignment
                cell.border = thin_border
                cell.font = songti_font
        output = BytesIO()
        wb.save(output)
        output.seek(0)
        return send_file(
            output,
            as_attachment=True,
            download_name=download_name
        )

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/generate/introspection/record', methods=["GET"])
@login_required
@role_required('admin', 'department_head', 'group_leader')
@require_params('role', 'teacherId', 'semester', 'courseName', 'className', 'week', 'materialType')
def generate_introspection_record():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        week = int(params.get("week"))
        material_type = params.get("materialType")
        if role not in ["admin", "department_head", "group_leader"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        file_path = material_query.get_file_path(teacher_name, semester, course_name, class_name, week, material_type)
        result = send_introspection_request(file_path)
        return jsonify({
            "code": 200,
            "data": result,
            "message": "success"
        })

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/generate/content/record', methods=["GET"])
@login_required
@role_required('admin', 'department_head', 'group_leader')
@require_params('role', 'teacherId', 'semester', 'courseName', 'className', 'week', 'materialType')
def generate_content_record():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        week = int(params.get("week"))
        material_type = params.get("materialType")
        if role not in ["admin", "department_head", "group_leader"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        file_path = material_query.get_file_path(teacher_name, semester, course_name, class_name, week, material_type)
        result = send_content_request(file_path)
        return jsonify({
            "code": 200,
            "data": result,
            "message": "success"
        })

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@material.route('/generate/comparison/record', methods=["GET"])
@login_required
@role_required('admin', 'department_head', 'group_leader')
@require_params('role', 'teacherId', 'semester', 'courseName', 'className', 'week', 'materialType')
def generate_comparison_record():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        course_name = params.get("courseName")
        class_name = params.get("className")
        week = int(params.get("week"))
        material_type = params.get("materialType")
        if role not in ["admin", "department_head", "group_leader"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})

        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        file_path = material_query.get_file_path(teacher_name, semester, course_name, class_name, week, material_type)
        lesson_plan_file_path = material_query.get_file_path(teacher_name, semester, course_name, class_name, -1,
                                                             'lesson_plan')
        file_path = f'{file_path},{lesson_plan_file_path}'
        result = send_comparison_request(file_path)
        return jsonify({
            "code": 200,
            "data": result,
            "message": "success"
        })

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e




def send_introspection_request(file_path: str):
    try:
        result = asyncio.run(async_send_introspection_request(file_path))
        if result:
            introspection_result = result.get('answer')
            simp_result = parse_ai_check_result(introspection_result)
            material_query.update_introspection_result(file_path, simp_result)
        return result
    except Exception as e:
        return None


def send_content_request(file_path: str):
    try:
        # 获取数据
        result = asyncio.run(async_send_content_request(file_path))
        # 将数据入库
        if result:
            check_detail = result.get("data").get("check_result")
            check_result = extract_conclusion(check_detail).replace('*', '')
            material_query.update_content_result(file_path, check_detail, check_result)
        return result
    except Exception as e:
        return None


def send_comparison_request(file_path: str):
    try:
        # 在线程池中运行异步函数
        result = asyncio.run(async_send_comparison_request(file_path))
        if result:
            check_detail = result.get("data", {}).get("check_result")
            check_result = extract_conclusion(check_detail)
            material_query.update_comparison_result(file_path, check_detail, check_result)
        return result
    except Exception as e:
        return None


async def async_send_introspection_request(file_path: str):
    url = "http://10.189.3.17/v1/chat-messages"
    headers = {
        "Authorization": "Bearer app-lepJizOqTNbY9fGjUsPOEekV",
        "Content-Type": "application/json"
    }
    data = {
        "response_mode": "blocking",
        "conversation_id": "",
        "files": [],
        "query": 1,
        "inputs": {
            "file_path": file_path
        },
        "user": "abc"
    }
    connector = aiohttp.TCPConnector(verify_ssl=False)
    timeout = aiohttp.ClientTimeout(total=10)

    async with aiohttp.ClientSession(connector=connector, timeout=timeout) as session:
        try:
            async with session.post(url, headers=headers, json=data) as response:
                response.raise_for_status()
                return await response.json()
        except Exception as e:
            raise e


async def async_send_content_request(file_path: str):
    url = "https://10.189.3.16:3333/cherry/workflows/lesson_plan_check/run"
    headers = {
        "Authorization": "Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJhZG1pbiIsInJvbGUiOiJhZG1pbiIsInR5cGUiOiJhY2Nlc3MiLCJleHAiOjE3NjM2MzI4MTEsImlhdCI6MTc2MzYzMTAxMSwiaXNzIjoibHh4In0.ZHvYbvV41cr2SdCu4qalubSLsWPAy7mA5We18bGFQ9E",
        "Content-Type": "application/json"
    }
    data = {
        "file_path": file_path
    }
    connector = aiohttp.TCPConnector(verify_ssl=False)
    timeout = aiohttp.ClientTimeout(total=10)

    async with aiohttp.ClientSession(connector=connector, timeout=timeout) as session:
        try:
            async with session.post(url, headers=headers, json=data) as response:
                response.raise_for_status()
                return await response.json()
        except Exception as e:
            raise e


async def async_send_comparison_request(file_path: str):
    url = "https://10.189.3.16:3333/cherry/workflows/lesson_plan_ccomparison/run"
    headers = {
        "Authorization": "Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJhZG1pbiIsInJvbGUiOiJhZG1pbiIsInR5cGUiOiJhY2Nlc3MiLCJleHAiOjE3NjM2MzI4MTEsImlhdCI6MTc2MzYzMTAxMSwiaXNzIjoibHh4In0.ZHvYbvV41cr2SdCu4qalubSLsWPAy7mA5We18bGFQ9E",
        "Content-Type": "application/json"
    }
    data = {
        "file_path": file_path
    }

    connector = aiohttp.TCPConnector(verify_ssl=False)
    timeout = aiohttp.ClientTimeout(total=30)

    async with aiohttp.ClientSession(connector=connector, timeout=timeout) as session:
        try:
            async with session.post(url, headers=headers, json=data) as response:
                response.raise_for_status()
                return await response.json()
        except Exception as e:
            raise e


def get_submission_by_material_type_for_dept(dept_id: str, semester: str, material_type: str) -> List[Dict[str, Any]]:
    if material_type == "teaching_plan":
        data = material_query.get_teaching_plan_submission_by_dept(dept_id, semester)
    elif material_type == "score_report" or material_type == "training_plan" or material_type == "lesson_plan":
        data = material_query.get_course_submission_by_dept(dept_id, semester, material_type)
    else:
        data = material_query.get_submission_by_dept(dept_id, semester, material_type)
    return data


def style_cell(cell, font=None, align=True):
    default_font = Font(name='楷体', size=16)
    center_align = Alignment(horizontal='center', vertical='center')
    if font:
        cell.font = font
    else:
        cell.font = default_font
    if align:
        cell.alignment = center_align


def handle_single_material(teacher_name: str, semester: str, course_name: str, class_name: str, week: int,
                           material_type: str, file_one: FileStorage):
    try:
        if file_one is None or file_one.filename == '':
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件为空"})

        cour_dept_and_inst = course_query.get_course_department_and_instructor_by_semester_and_course_name_and_class_name(
            semester, course_name, class_name)
        course_dept = cour_dept_and_inst["course_department"]
        if not course_dept:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "获取课程所属系部失败"})

        file_path = file.get_workdir_file_path(
            semester, course_dept, material_type, course_name, class_name, teacher_name
        )
        # 文件扩展名
        file_extension = get_file_extension(file_one.filename)
        file_name = file.get_file_name(
            material_type, semester, course_name, class_name, teacher_name, week, file_extension
        )
        # 如果提交过，执行更新操作
        if material_query.get_submission_existence('', semester, course_name, class_name, week,
                                                   material_type):
            remove_origin_file('', semester, course_name, class_name, week, material_type)
            material_query.update_submission(teacher_name, semester, course_name, class_name, week,
                                             material_type, os.path.join(file_path, file_name))
        else:
            material_query.submit(
                teacher_name, semester, course_name, course_dept, class_name, week, material_type,
                os.path.join(file_path, file_name)
            )
        file.upload_file(file_one, file_path, file_name)
        return {
            "message": "上传成功",
            "success": True
        }
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


def remove_origin_file(teacher_name, semester, course_name, class_name, week,
                       material_type):
    orign_path = material_query.get_file_path(teacher_name, semester, course_name, class_name, week,
                                              material_type)
    # 直接删掉旧文件， 如果要完善：应该再加个判断，如果扩展名和之前不一样才执行删除
    if orign_path:
        file.remove_file(str(Path(orign_path).resolve()))


def get_submission_by_material_type_for_teacher(instructor: str, semester: str, course_name: str, class_name: str,
                                                material_type: str) -> List[Dict[str, Any]]:
    if material_type == "teaching_plan":
        data = material_query.get_teaching_plan_submission_by_teacher(
            instructor, semester, course_name, class_name
        )
    elif material_type in ["score_report", "training_plan", "lesson_plan"]:
        data = material_query.get_course_submission_by_teacher(
            instructor, semester, course_name, class_name, material_type
        )
    else:
        data = material_query.get_submission_by_teacher(
            instructor, semester, course_name, class_name, material_type
        )
    return data


# 返回当前周数，-1表示该学期未开始，22表示该学期可能进入最后一周或者已结束
def parse_current_week_range(semester: str) -> int:
    current_date = datetime.now().date()
    row = classes_query.get_current_week(semester)
    max_end_date = ''
    for week_num in range(1, 23):
        week_field = f"week{week_num:02d}"
        week_date_range = row[week_field]

        if not week_date_range:
            continue
        start_str, end_str = week_date_range.split("-", 1)
        start_str = start_str.strip().replace('.', '-')
        end_str = end_str.strip().replace('.', '-')

        start_date = datetime.strptime(start_str, '%Y-%m-%d').date()
        end_date = datetime.strptime(end_str, '%Y-%m-%d').date()
        if week_num == 22:
            max_end_date = end_date
        if start_date <= current_date <= end_date:
            return week_num
    if current_date > max_end_date:
        return 22
    return -1


def safe_decode_zip_filename(filename: str) -> str:
    """
    安全的 ZIP 文件名解码函数
    尝试多种编码方式处理包含中文的文件名
    """
    # 尝试常见的编码组合
    encoding_attempts = [
        ('cp437', 'gbk'),  # Windows 中文系统常见
        ('cp437', 'gb2312'),  # 早期中文系统
        ('cp437', 'utf-8'),  # 现代系统
        ('cp850', 'gbk'),  # 其他 DOS 编码
        ('iso-8859-1', 'gbk'),  # Latin-1 转中文
    ]

    # 尝试各种编码组合
    for encode_from, encode_to in encoding_attempts:
        try:
            return filename.encode(encode_from).decode(encode_to)
        except (UnicodeEncodeError, UnicodeDecodeError):
            continue

    # 最后尝试：移除无法编码的字符
    try:
        return filename.encode('ascii', errors='ignore').decode('ascii')
    except:
        # 如果所有方法都失败，返回原始文件名
        return filename
